using System;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Web;
using MySql.Data.MySqlClient;
namespace LdExpress
{
    /// <summary>
    /// Trucker
    /// </summary>
    public class Trucker
    {
        public long ID = 0;
        public string BizName = "";
        public long LocationID = 0;
        public string FullName = "";
        public string InvoicingName = "";
        public string Address = "";
        public string MST = "";
        public string Note = "";
        public DateTime LastUpdate = DateTime.Now;
        public Trucker()
        {
        }
        public Trucker(long lID)
        {
            this.ID = lID;
        }
        public void Save(MySqlConnection cn, MySqlTransaction trans)
        {
            string sqlCommand = "";
            if (ID <= 0) // new object, we insert new record to database
            {
                // build SQL statement
                sqlCommand = "Insert into tbl_Trucker (" +
                             "BizName," +
                             "LocationID," +
                             "FullName," +
                             "InvoicingName," +
                             "Address," +
                             "MST," +
                             "Note," +
                             "LastUpdate) " +
                         " values(" +
                             "'" + DBModule.RefineString(BizName) + "'," +
                             LocationID.ToString() + "," +
                             "'" + DBModule.RefineString(FullName) + "'," +
                             "'" + DBModule.RefineString(InvoicingName) + "'," +
                             "'" + DBModule.RefineString(Address) + "'," +
                             "'" + DBModule.RefineString(MST) + "'," +
                             "'" + DBModule.RefineString(Note) + "'," +
                             DBModule.RefineDatetime(LastUpdate) + ")";
                // run SQL statement
                DBModule.ExecuteNonQuery(sqlCommand, cn, trans);
                UserObjectsLog.SaveObjectActionLog("Trucker", "add", cn, trans);                
            }
            else // edit object, we update old record in database
            {
                // build SQL statement
                sqlCommand = "Update tbl_Trucker set " +
                             "BizName='" + DBModule.RefineString(BizName) + "'," +
                             "LocationID='" + LocationID.ToString() + "'," +
                             "FullName='" + DBModule.RefineString(FullName) + "'," +
                             "InvoicingName='" + DBModule.RefineString(InvoicingName) + "'," +
                             "Address='" + DBModule.RefineString(Address) + "'," +
                             "MST='" + DBModule.RefineString(MST) + "'," +
                             "Note='" + DBModule.RefineString(Note) + "'," +
                             "LastUpdate=" + DBModule.RefineDatetime(LastUpdate) + "" +
                    " where ID=" + ID.ToString();
                // run SQL statement
                DBModule.ExecuteNonQuery(sqlCommand, cn, trans);
                UserObjectsLog.SaveObjectActionLog("Trucker", "edit", cn, trans);                
            }
        }
        public void Delete(MySqlConnection cn, MySqlTransaction trans)
        {
            //Buil SQL Statement
            //Run SQL Statement
            string sqlCommand = "DELETE FROM tbl_Trucker WHERE ID = " + this.ID.ToString() + " limit 1";
            DBModule.ExecuteNonQuery(sqlCommand, cn, trans);
            UserObjectsLog.SaveObjectActionLog("Trucker", "delete", cn, trans);
        }
        public void Load(MySqlConnection cn, MySqlTransaction trans)
        {
            string sqlCommand = "";
            //Build SQL Statement
            //Run SQL Statement
            sqlCommand = "SELECT * FROM tbl_Trucker WHERE ID = " + ID.ToString() + " limit 0,1";
            DataSet ds = DBModule.ExecuteQuery(sqlCommand, cn, trans);
            //Fill data into this object
            if (ds.Tables[0].Rows.Count > 0)
            {
                DataRow dr = ds.Tables[0].Rows[0];
                if (!dr.IsNull("ID"))
                    ID = long.Parse(dr["ID"].ToString());
                if (!dr.IsNull("BizName"))
                    BizName = dr["BizName"].ToString();
                if (!dr.IsNull("LocationID"))
                    LocationID = long.Parse(dr["LocationID"].ToString());
                if (!dr.IsNull("FullName"))
                    FullName = dr["FullName"].ToString();
                if (!dr.IsNull("InvoicingName"))
                    InvoicingName = dr["InvoicingName"].ToString();
                if (!dr.IsNull("Address"))
                    Address = dr["Address"].ToString();
                if (!dr.IsNull("MST"))
                    MST = dr["MST"].ToString();
                if (!dr.IsNull("Note"))
                    Note = dr["Note"].ToString();
                if (!dr.IsNull("LastUpdate"))
                    LastUpdate = DateTime.Parse(dr["LastUpdate"].ToString());
            }
            else
            {
            }
        }
        public static void GetList(string condittion, string OrderBy, int NumOfItemsPerPage, int PageNo, out int NumberOfPages, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            NumberOfPages = 0;
            string sqlCommand = "";
            ds = null;
            // build SQL statement
            sqlCommand = "Select tbl_Trucker.*,LC.Name as LocationName " +
                " from tbl_Trucker" +
                " Left Join tbl_Location LC on tbl_Trucker.LocationID = LC.ID" +
                " where 1=1 ";
            if (condittion.Length > 0)
            {
                sqlCommand += condittion;
            }

            if ((OrderBy != null) && (OrderBy != ""))
                sqlCommand = sqlCommand + " Order By " + DBModule.RefineString(OrderBy);
            // run SQL statement
            if (NumOfItemsPerPage > 0)
            {
                ds = DBModule.ExecuteQuery(sqlCommand, NumOfItemsPerPage, PageNo, out NumberOfPages, cn, trans);
            }
            else
                ds = DBModule.ExecuteQuery(sqlCommand, cn, trans);
        }
        public static void DoSearch(string keywords, string OrderBy, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            string sqlCommand = "";
            ds = null;
            // build SQL statement
            sqlCommand = "Select `ID` from tbl_Trucker where 1=1 ";

            if ((OrderBy != null) && (OrderBy != ""))
                sqlCommand = sqlCommand + " Order By " + DBModule.RefineString(OrderBy);
            // run SQL statement
            ds = DBModule.ExecuteQuery(sqlCommand, cn, trans);
        }
        public static int isExisting(long inputID, string inputMST, MySqlConnection cn, MySqlTransaction trans)
        {
            string sqlCommand = "Select ID from tbl_Trucker where MST='" + DBModule.RefineString(inputMST) + "' AND ID <>" + inputID + " limit 1";
            DataSet ds = DBModule.ExecuteQuery(sqlCommand, cn, trans);
            if (ds.Tables[0].Rows.Count > 0)
                return 1;
            else
                return 0;
        }
    }
}